﻿using Dapper;
using Infrastructure.Models;
using Infrastructure.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace Infrastructure.DAL
{
    /// <summary>
    /// 配方DAL
    /// </summary>
    public class FormulaDAL
    {
        public static int AddOrUpdate(FormulaTpl tpl)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                int rows = 0;
                int id = tpl.id;
                if (tpl.id == 0)
                {

                    //插入设备表的记录
                    id = conn.Query<int>(@"insert into formula_tpl(device_id,template_name,create_time,create_user,update_time,update_user)
values(@device_id,@template_name,@create_time,@create_user,@update_time,@update_user);
select last_insert_rowid() from formula_tpl; ", tpl, tran).FirstOrDefault();
                    if (id > 0)
                        rows = 1;
                }
                else
                {

                    //修改
                    rows = conn.Execute(@"update formula_tpl set 
template_name=@template_name,
update_time=@update_time,
update_user=@update_user where id=@id ", tpl, tran);
                }
                conn.Execute("delete from formula_tpl_param where tpl_id=@tpl_id", new { tpl_id = id });
                int i = 1;
                //插入设备关联的变量记录
                foreach (var item in tpl.ParamList)
                {
                    item.tpl_id = id;
                    item.param_field = "p"+i;//设置动态字段值
                    rows += conn.Execute(@"insert into formula_tpl_param(tpl_id,param_name,param_type,param_address,verify_address,param_field,create_time,create_user,update_time,update_user) 
values(@tpl_id,@param_name,@param_type,@param_address,@verify_address,@param_field,@create_time,@create_user,@update_time,@update_user);", item, tran);
                    i++;
                }
                tran.Commit();
                return rows;

            }
        }

        public static List<FormulaTpl> GetFormulaTpls(string tplName, out int total, int currentPage, int pageSize)
        {
            using (IDbConnection conn =
               new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = "select m.*,t0.device_name from formula_tpl m left join device t0 on m.device_id =t0.id where 1=1 ";
                if (!string.IsNullOrEmpty(tplName))
                {
                    sql += " and m.template_name like @tname ";
                }
                string totalSql = "select count(*) from ("+sql+") ";
                sql += @" order by id limit @size offset (@page-1)*@size ";
                total= conn.Query<int>(totalSql, new { tname = "%" + tplName + "%" }).FirstOrDefault();
                return conn.Query<FormulaTpl>(sql, new { tname = "%" + tplName + "%",page=currentPage,size=pageSize }).ToList();
            }
        }

        public static List<Formula> GetFormulaList(int id)
        {
            using (IDbConnection conn =
               new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = "select m.*,t0.template_name from formula m left join formula_tpl t0 on m.formula_tpl_id=t0.id where formula_tpl_id=@tpl_id;";
                return conn.Query<Formula>(sql, new { tpl_id = id }).ToList();
            }
        }

        public static List<FormulaTplParam> GetFormulaTplParamsBy(int id)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = "select * from formula_tpl_param where tpl_id=@id;";
                return conn.Query<FormulaTplParam>(sql, new { id = id }).ToList();
            }
        }

        public static List<FormulaTpl> GetFormulaTpls(string tplName)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                string sql = "select m.*,t0.device_name from formula_tpl m left join device t0 on m.device_id =t0.id where 1=1 ";
                if (!string.IsNullOrEmpty(tplName))
                {
                    sql += " and m.template_name like @tname ";
                }
                return conn.Query<FormulaTpl>(sql,new { tname="%"+tplName+"%"}).ToList();
            }
        }

        public static int DeleteTpl(int id)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                //开启事务删除关联表信息
                var tran = conn.BeginTransaction();
                
                //删除配方内容
               int rows= conn.Execute("delete from formula where formula_tpl_id=@tpl_id",new {tpl_id=id }, tran);
                //删除参数设置
                rows += conn.Execute("delete from formula_tpl_param where tpl_id=@tpl_id", new { tpl_id = id }, tran);
                //删除配方模板
               rows+= conn.Execute("delete from formula_tpl where id=@id",new { id=id},tran);
                tran.Commit();
                return rows;
            }
        }

        public static int SaveFormula(Formula obj)
        {
            using (IDbConnection conn =
               new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                int rows = 0;
                if (obj.id == 0)
                {
                    //插入设备表的记录
                    rows = conn.Execute(@"insert into formula(p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,
p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,formula_tpl_id,formula_name,create_time,create_user,update_time,update_user)
values(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,
@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@formula_tpl_id,@formula_name,@create_time,@create_user,@update_time,@update_user); ", obj, tran);
                }
                else
                {
                    rows = conn.Execute(@"update formula set p1=@p1,p2=@p2,p3=@p3,p4=@p4,p5=@p5,
p6=@p6,p7=@p7,p8=@p8,p9=@p9,p10=@p10,
p11=@p11,p12=@p12,p13=@p13,p14=@p14,p15=@p15,p16=@p16,
p17=@p17,p18=@p18,p19=@p19,p20=@p20,p21=@p21,p22=@p22,p23=@p23,p24=@p24,p25=@p25,
formula_name=@formula_name,update_time=@update_time,update_user=@update_user where id=@id; ", obj, tran);
                }
                tran.Commit();
                return rows;

            }
        }
    }
}
